"""
Excel文件转SQLite数据库

本脚本将Excel文件中的所有工作表导入到SQLite数据库中。

使用方法：
    1. 将Excel文件放在与脚本同一目录下
    2. 运行脚本，指定Excel文件路径和数据库文件路径（可选）

参数：
    excel_file_path: Excel文件路径
    db_file_path: SQLite数据库文件路径（可选）
    table_prefix: 表名前缀（可选）
返回：
    dict: 包含导入结果的字典
"""

import pandas as pd
import sqlite3
import numpy as np
from datetime import datetime
import os
import sys

def excel_to_sqlite(excel_file_path, db_file_path=None, table_prefix=""):
    """
    将Excel文件中的所有工作表导入到SQLite数据库

    参数:
        excel_file_path: Excel文件路径
        db_file_path: SQLite数据库文件路径（默认为Excel同目录下的同名.db文件）
        table_prefix: 表名前缀（可选）

    返回:
        dict: 包含导入结果的字典
    """

    def infer_sqlite_type(dtype, sample_data):
        """
        智能推断SQLite字段类型
        基于pandas数据类型和实际数据样本
        """
        # 处理空值情况
        if pd.isna(sample_data):
            return 'TEXT'  # 默认类型

        # 基于数据类型和实际值进行推断
        if np.issubdtype(dtype, np.integer):
            return 'INTEGER'
        elif np.issubdtype(dtype, np.floating):
            return 'REAL'
        elif np.issubdtype(dtype, np.datetime64):
            return 'DATETIME'
        elif isinstance(sample_data, (datetime, pd.Timestamp)):
            return 'DATETIME'
        elif isinstance(sample_data, (int, np.integer)):
            return 'INTEGER'
        elif isinstance(sample_data, (float, np.floating)):
            return 'REAL'
        elif isinstance(sample_data, bool):
            return 'BOOLEAN'
        else:
            return 'TEXT'

    def clean_table_name(name):
        """
        清理表名，移除非法字符
        """
        # 移除或替换SQLite不支持的字符
        cleaned = ''.join(
            c if c.isalnum() or c == '_' else '_' for c in str(name)
        )
        # 确保不以数字开头
        if cleaned and cleaned[0].isdigit():
            cleaned = 'tbl_' + cleaned
        return cleaned

    def create_table_schema(df, table_name):
        """
        创建表结构定义
        """
        columns = []
        for col in df.columns:
            cleaned_col = clean_table_name(col)
            sample_value = df[col].iloc[0] if len(df) > 0 else None
            col_type = infer_sqlite_type(df[col].dtype, sample_value)
            columns.append(f'"{cleaned_col}" {col_type}')

        create_sql = f'CREATE TABLE IF NOT EXISTS "{table_name}" (\n    '
        create_sql += ',\n    '.join(columns)
        create_sql += '\n)'

        return create_sql

    # 设置默认数据库文件路径
    if db_file_path is None:
        base_name = os.path.splitext(excel_file_path)[0]
        db_file_path = f"{base_name}.db"

    # 读取Excel文件
    try:
        excel_file = pd.ExcelFile(excel_file_path)
        sheet_names = excel_file.sheet_names
        print(f"找到 {len(sheet_names)} 个工作表: {sheet_names}")
    except Exception as e:
        print(f"读取Excel文件失败: {e}")
        return {"error": str(e)}

    results = {
        "total_sheets": len(sheet_names),
        "successful_imports": 0,
        "failed_imports": 0,
        "details": []
    }

    conn = None
    try:
        # 连接到SQLite数据库
        conn = sqlite3.connect(db_file_path)
        cursor = conn.cursor()

        for sheet_name in sheet_names:
            try:
                print(f"正在处理工作表: {sheet_name}")

                # 读取工作表数据
                df = pd.read_excel(excel_file_path, sheet_name=sheet_name)

                if df.empty:
                    print(f"工作表 '{sheet_name}' 为空，跳过")
                    results["details"].append({
                        "sheet_name": sheet_name,
                        "status": "skipped",
                        "reason": "空工作表"
                    })
                    continue

                # 清理列名
                df.columns = [clean_table_name(col) for col in df.columns]

                # 处理表名
                table_name = clean_table_name(f"{table_prefix}{sheet_name}")

                # 创建表结构
                create_sql = create_table_schema(df, table_name)
                cursor.execute(create_sql)

                # 插入数据
                # 处理日期时间类型
                for col in df.columns:
                    if df[col].dtype == 'datetime64[ns]':
                        df[col] = df[col].dt.strftime('%Y-%m-%d %H:%M:%S')
                    # 将NaN替换为None（SQL NULL）
                    df[col] = df[col].where(pd.notna(df[col]), None)

                # 构建插入SQL
                placeholders = ', '.join(['?' for _ in df.columns])
                insert_sql = f'INSERT INTO "{table_name}" VALUES ({placeholders})'

                # 插入数据
                data_tuples = [tuple(row) for row in df.values]
                cursor.executemany(insert_sql, data_tuples)

                # 提交事务
                conn.commit()

                print(
                    f"成功导入工作表 '{sheet_name}' 到表 '{table_name}'，共 {len(df)} 行数据"
                )

                results["successful_imports"] += 1
                results["details"].append({
                    "sheet_name": sheet_name,
                    "table_name": table_name,
                    "status": "success",
                    "rows_imported": len(df),
                    "columns": list(df.columns)
                })

            except Exception as e:
                print(f"导入工作表 '{sheet_name}' 失败: {e}")
                if conn:
                    conn.rollback()

                results["failed_imports"] += 1
                results["details"].append({
                    "sheet_name": sheet_name,
                    "status": "failed",
                    "reason": str(e)
                })

        print(
            f"\n导入完成！成功: {results['successful_imports']}, 失败: {results['failed_imports']}"
        )
        print(f"数据库文件: {db_file_path}")

    except Exception as e:
        print(f"数据库操作失败: {e}")
        results["error"] = str(e)
    finally:
        if conn:
            conn.close()

    return results

if __name__ == "__main__":
    # 命令行参数解析，支持3种参数模式
    if len(sys.argv) >= 4:
        result = excel_to_sqlite(
            excel_file_path=sys.argv[1],
            db_file_path=sys.argv[2],
            table_prefix=sys.argv[3]
        )
    elif len(sys.argv) == 3:
        result = excel_to_sqlite(
            excel_file_path=sys.argv[1],
            db_file_path=sys.argv[2]
        )
    elif len(sys.argv) == 2:
        result = excel_to_sqlite(
            excel_file_path=sys.argv[1]
        )
    else:
        print("用法: python xlsx2sqlite.py <excel_file_path> [db_file_path] [table_prefix]")
        sys.exit(1)

    # 打印详细结果
    print("\n导入详情:")
    for detail in result.get("details", []):
        print(f"  {detail['sheet_name']}: {detail['status']}")